<?php
	define('CONFIG_FILE',true);
	include '../config.php';
	
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';

	$sqlDBManager = new SqlDBManager();

	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}

	if (!empty($_SESSION["userType"])) {
	    $userType = $_SESSION["userType"];
	}
	if (!empty($_SESSION["centreName"])) {
	    $centreName = $_SESSION["centreName"];
	}

	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 20;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'NRIC';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
	$searchid='';
	if(isset($_POST['searchid'])){
		$searchid = $_POST['searchid'];
	}
	$offset = ($page - 1) * $rows;
	$result = array();

	//Filter the $searchid
	$searchid=addslashes($searchid);
	$searchid=str_replace("%", "\%", $searchid);
	$searchid=str_replace("_", "\_", $searchid);

	if ($userType == "System Admin") {
        $sql ="SELECT count(*)
			FROM client
			INNER JOIN Centre ON client.CentreId = centre.CentreId
			WHERE centre.CentreId IS NOT NULL
			AND (
			client.NRIC LIKE ?
			OR client.CustomerName LIKE ?
			OR client.Citizenship LIKE ?
			OR client.Daycare LIKE ?
			OR client.BeforeDeviation LIKE ?
			OR client.AfterDeviation LIKE ?
			OR client.DateOfBirth LIKE  ?
			OR client.Gender LIKE ?)";

		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
        $res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}

        $sql ="SELECT client.NRIC, client.CustomerName, client.Citizenship, client.Daycare,client.BeforeDeviation, client.AfterDeviation, client.DateOfBirth, client.Gender, centre.CentreName
			FROM client
			INNER JOIN Centre ON client.CentreId = centre.CentreId
			WHERE client.NRIC LIKE  ?
			OR client.CustomerName LIKE ?
			OR client.Citizenship LIKE  ?
			OR client.Daycare LIKE  ?
			OR client.BeforeDeviation LIKE  ?
			OR client.AfterDeviation LIKE  ?
			OR client.DateOfBirth LIKE  ?
			OR client.Gender LIKE  ?
			OR centre.CentreName LIKE  ?
			AND client.CentreId IS NOT NULL order by $sort $order limit $offset,$rows";	    

		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
		$items = array();
		
		if(!empty($res)){
			for($i=0;$i<count($res);$i++){
				array_push($items, $res[$i]);
			}	
		}
		
	    //close connection
		$sqlDBManager->close_connect();

		$result["rows"] = $items;
	    echo json_encode($result);
	}else{
		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);

	    $sql ="SELECT count(*)
			FROM client
			INNER JOIN Centre ON client.CentreId = centre.CentreId
			WHERE centre.CentreName =?
			AND (
			client.NRIC LIKE ?
			OR client.CustomerName LIKE ?
			OR client.Citizenship LIKE ?
			OR client.Daycare LIKE ?
			OR client.BeforeDeviation LIKE ?
			OR client.AfterDeviation LIKE ?
			OR client.DateOfBirth LIKE  ?
			OR client.Gender LIKE ?)";
		
		$parameters = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}

		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
	    $sql ="SELECT client.NRIC, client.CustomerName, client.Citizenship,client.Daycare, client.BeforeDeviation, client.AfterDeviation, client.DateOfBirth, client.Gender, centre.CentreName
			FROM client
			INNER JOIN Centre ON client.CentreId = centre.CentreId
			WHERE centre.CentreName = ?
			AND (
			client.NRIC LIKE ?
			OR client.CustomerName LIKE ?
			OR client.Citizenship LIKE  ?
			OR client.Daycare LIKE  ?
			OR client.BeforeDeviation LIKE  ?
			OR client.AfterDeviation LIKE  ?
			OR client.DateOfBirth LIKE ?
			OR client.Gender LIKE ?
			) order by $sort $order limit $offset,$rows";
		
		$parameters = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
		$items = array();
		
		if(!empty($res)){
			for($i=0;$i<count($res);$i++){
				array_push($items, $res[$i]);
			}	
		}

		//close connection
		$sqlDBManager->close_connect();

	    $result["rows"] = $items;
	    echo json_encode($result);
	}
?>